﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entity;

namespace DAL.Persistencia
{
    public class HolidaysDAL
    {

        private NewTierraEntities Con;

        public HolidaysDAL()
        {
            Con = new NewTierraEntities();
        }

        public void Salvar(Holidays h)
        {
            try
            {
                Con.AddToHolidays(h);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public Holidays ObterPorId(int IdHoli)
        {
            try
            {
                return Con.Holidays.Where(h => h.Holidays_id == IdHoli).SingleOrDefault();
            }
            catch 
            {                
                throw;
            }
        }

        public Holidays ObterPorIdServ(int IdServ)
        {
            try
            {
                return Con.Holidays.Where(h => h.Servicos_Id == IdServ).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public void Excluir(Holidays h)
        {
            try
            {
                Con.Holidays.DeleteObject(h);
                Con.SaveChanges();
            }
            catch 
            {                
                throw;
            }
        }

        public void Atualizar(Holidays novo)
        {
            try
            {
                Holidays antigo = ObterPorId(novo.Holidays_id);

                antigo.Holidays_seg     = novo.Holidays_seg;
                antigo.Holidays_ter     = novo.Holidays_ter;
                antigo.Holidays_qua     = novo.Holidays_qua;
                antigo.Holidays_qui     = novo.Holidays_qui;
                antigo.Holidays_sex     = novo.Holidays_sex;
                antigo.Holidays_sab     = novo.Holidays_sab;
                antigo.Holidays_dom     = novo.Holidays_dom;
                antigo.Holidays_remarks = novo.Holidays_remarks;

                Con.SaveChanges();

            }
            catch 
            {                
                throw;
            }
        }

        public bool VerificaHoliday(DateTime dataFrom, int IdServico)
        {
            try
            {
                return (from hol in Con.Holidays
                        join hda in Con.Holidays_Dates on hol.Holidays_id equals hda.Holidays_id
                        where hol.Servicos_Id == IdServico &&
                              hda.Holidays_Dates_data == dataFrom
                        select hol).Count() != 0;
            }
            catch 
            {                
                throw;
            }
        }

        public bool VerificaHolidayEntreDatas(DateTime dataFrom, int IdServico)
        {
            try
            {
                return (from hol in Con.Holidays
                        join hda in Con.Holidays_Dates on hol.Holidays_id equals hda.Holidays_id
                        join hpo in Con.Holidays_Periodo on hol.Holidays_id equals hpo.Holidays_id
                        where hol.Servicos_Id == IdServico &&
                              hpo.HoliDays_Periodo_from <= dataFrom &&
                              hpo.HoliDays_Periodo_to >= dataFrom
                        select hol).Count() != 0;
            }
            catch
            {
                throw;
            }
        }

        public bool VerificaHolidaySemana(DateTime dataFrom, int IdServico)
        {
            SqlCon conx = new SqlCon();
            try
            {

                string dia = ReturnDayPT(dataFrom.DayOfWeek);                

                conx.AbrirConexao();

                string sql = "SELECT Holidays_id, Servicos_Id, Holidays_seg, Holidays_ter, Holidays_qua, Holidays_qui, Holidays_sex, Holidays_sab, Holidays_dom, Holidays_remarks ";
                sql = sql + "FROM Holidays WHERE (Servicos_Id = " + IdServico + ") AND (" + dia + " = 'True')";                
                
                conx.Cmd = new System.Data.SqlClient.SqlCommand(sql, conx.Con);
                
                conx.Dr = conx.Cmd.ExecuteReader();
                
                if(conx.Dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                conx.FecharConexao();
            }
        }

        public string ReturnDayPT(DayOfWeek day)
        {
            switch (day)
            {
                case DayOfWeek.Monday:
                    return "Holidays_seg";
                case DayOfWeek.Tuesday:
                    return "Holidays_ter";
                case DayOfWeek.Wednesday:
                    return "Holidays_qua";
                case DayOfWeek.Thursday:
                    return "Holidays_qui";
                case DayOfWeek.Friday:
                    return "Holidays_sex";
                case DayOfWeek.Saturday:
                    return "Holidays_sab";
                case DayOfWeek.Sunday:
                    return "Holidays_dom";
                default:
                    return "Holidays_dom";
            }
        }

    }
}
